﻿using System;

using System.Data;

using System.Data.Common;

using System.Data.SQLite;
using System.Windows.Forms;

namespace RuoTeng.Util
{
     
    public class SQLiteHelper
    {

        private string connectionString = string.Empty;

        private string rootpath = Application.StartupPath;//程序的根路径

        private string dbpath = "MailDB.sqlite";


        /// <summary> 

        /// 构造函数 

        /// </summary> 

        /// <param name="dbPath">SQLite数据库文件路径</param> 

        public SQLiteHelper()
        {
            this.connectionString = "Data Source=" +rootpath+"\\"+ dbpath;
        }

        
        /// <summary> 

        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 

        /// </summary> 

        /// <param name="sql">要执行的增删改的SQL语句</param> 

        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 

        /// <returns></returns> 

        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {

            int affectedRows = 0;

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {

                connection.Open();

                using (DbTransaction transaction = connection.BeginTransaction())
                {

                    using (SQLiteCommand command = new SQLiteCommand(connection))
                    {

                        command.CommandText = sql;

                        if (parameters != null)
                        {

                            command.Parameters.AddRange(parameters);

                        }

                        affectedRows = command.ExecuteNonQuery();

                    }

                    transaction.Commit();

                }

            }

            return affectedRows;

        }

        /// <summary> 

        /// 执行一个查询语句，返回一个关联的SQLiteDataReader实例 

        /// </summary> 

        /// <param name="sql">要执行的查询语句</param> 

        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 

        /// <returns></returns> 

        public SQLiteDataReader ExecuteReader(string sql, SQLiteParameter[] parameters)
        {

            SQLiteConnection connection = new SQLiteConnection(connectionString);

            SQLiteCommand command = new SQLiteCommand(sql, connection);

            if (parameters != null)
            {

                command.Parameters.AddRange(parameters);

            }

            connection.Open();

            return command.ExecuteReader(CommandBehavior.CloseConnection);

        }

        /// <summary> 

        /// 执行一个查询语句，返回一个包含查询结果的DataTable 

        /// </summary> 

        /// <param name="sql">要执行的查询语句</param> 

        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 

        /// <returns></returns> 

        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {

                using (SQLiteCommand command = new SQLiteCommand(sql, connection))
                {

                    if (parameters != null)
                    {

                        command.Parameters.AddRange(parameters);
                    }

                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);

                    DataTable data = new DataTable();

                    adapter.Fill(data);

                    return data;

                }

            }



        }

        /// <summary> 

        /// 执行一个查询语句，返回查询结果的第一行第一列 

        /// </summary> 

        /// <param name="sql">要执行的查询语句</param> 

        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 

        /// <returns></returns> 

       
        public   object ExecuteScalar( string commandText)  

        {  

            SQLiteConnection cn = new SQLiteConnection(connectionString);  

             SQLiteCommand cmd = cn.CreateCommand();  

            cmd.CommandText = commandText;  

         

           if (cn.State == ConnectionState.Closed)  

                 cn.Open();  

             object result = cmd.ExecuteScalar();  

             cmd.Dispose();  

            cn.Close();  

  

             return result;  

        }  

      

        /// <summary> 

        /// 查询数据库中的所有数据类型信息 

        /// </summary> 

        /// <returns></returns> 

        public DataTable GetSchema()
        {

            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {

                connection.Open();

                DataTable data = connection.GetSchema("TABLES");

                connection.Close();

                //foreach (DataColumn column in data.Columns) 

                //{ 

                //        Console.WriteLine(column.ColumnName); 

                //} 

                return data;

            }

        }

    }

}

